package zyw;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExtraTest {



    public static void main(String[] args) throws IOException {

        String inputFilePathA = "D:\\work\\临时文件\\zyw\\工作量\\工作量操作.xlsx";
//        String inputFilePathB = "D:\\work\\临时文件\\zyw\\往来\\往来1.xlsx";
//        String outputFilePath = "D:\\桌面文件\\临时文件\\zyw\\Z03.xlsx";
        int sheetA = 5;
        int sheetB = 2;

        List<JSONObject> dataA = new ArrayList<>();
        // 加载A文件
        try (FileInputStream fis = new FileInputStream(inputFilePathA);
             Workbook workbook = new XSSFWorkbook(fis)) {
            // 获取sheet索引从0开始
            Sheet sheet = workbook.getSheetAt(sheetA);

            // 跳过表头开始遍历
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row != null) {
                    // 假设目标是第三列（索引为2，因为索引从0开始）
                    Cell name = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell quxian = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell name = row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell je = row.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell id = row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell name = row.getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell g = row.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell h = row.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    String ht = "";
//                    if (h.getCellType() == CellType.NUMERIC) {
//                        // 转换Excel日期为Java日期
//                        Date javaDate = convertExcelDateToJavaDate(h.getNumericCellValue());
//
//                        // 格式化日期为字符串
//                        String formattedDate = formatDate(javaDate);
//                        ht = formattedDate;
//                        // 输出结果
//                        System.out.println(formattedDate);
//                    }
                    if (StringUtil.isNotBlank(name.toString())){
                        JSONObject data = new JSONObject();
//                    data.put("id",id.toString());
//                    data.put("name",name.toString());
//                    data.put("g",g.toString());
//                    data.put("h",ht);
//                        data.put("bh",bh.toString());
                        if (ObjectUtil.isNotNull(name)){
                            data.put("name",name.toString());
                            data.put("quxian",quxian.toString());
                        }

//                    data.put("je",je.toString());
                        dataA.add(data);
                    }
                }
            }
        }

        // 加载B文件
        try (FileInputStream fis = new FileInputStream(inputFilePathA);
             Workbook workbook = new XSSFWorkbook(fis)) {

            // 获取sheet
            Sheet sheet = workbook.getSheetAt(sheetB);
            // 在最后一列之后添加新列
            // 获取最后一列的索引
            int newColumnIndex = sheet.getRow(1).getLastCellNum();
            // 自动调整新列的大小（可选）
            sheet.autoSizeColumn(newColumnIndex);
            Row headerRow = sheet.getRow(0);
            if (headerRow != null) {
                // 在表头后面添加
                Cell headerCell = headerRow.createCell(headerRow.getLastCellNum(), CellType.STRING);
                headerCell.setCellValue("额外列");
            }

            // 遍历所有行，添加需要比较列的值
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row != null) {
                    // 假设目标列第三列（索引为2，因为索引从0开始）
                    Cell nameRes = row.getCell(9, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    Cell quxianRes = row.getCell(10, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell name = row.getCell(4, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell je = row.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell idCell = row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell g = row.getCell(6, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
//                    Cell h = row.getCell(7, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                    // 在新列上创建单元格
//                    Cell isAdultCell = row.createCell(newColumnIndex, CellType.STRING);

                    //开始逻辑判断
//                    for (JSONObject jsonObject : dataA) {
//                        if (idCell.toString().equals(jsonObject.getString("id"))){
////                            isAdultCell.setCellValue(jsonObject.getString("name"));
//                            g.setCellValue(jsonObject.getString("g"));
//                            h.setCellValue(jsonObject.getString("h"));
//                            break;
//                        }
//                    }

                    for (JSONObject jsonObject : dataA) {
                        if (!StringUtil.isBlank(nameRes.toString())){
                            String text = nameRes.toString();
//                            String bh = jsonObject.getString("bh");
                            String nameA = jsonObject.getString("name");
                            if (text.equals(nameA)){
                                System.out.println("匹配" + text);
                                quxianRes.setCellValue(jsonObject.getString("quxian"));
//                                isAdultCell.setCellValue("匹配bh");
//                                isAdultCell.setCellValue("俞波");
                                break;
                            }
                        }
                    }
                }
            }

            // 将修改后的workbook写入文件
            try (FileOutputStream fos = new FileOutputStream(inputFilePathA)) {
                workbook.write(fos);
            }

            // 输出提示信息
            System.out.println("处理完成，文件已保存到：" + inputFilePathA);
        }
    }

}
